﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhouYu.DreamTour.Core;
using ZhouYu.DreamTour.DAL.IRepository;
using ZhouYu.DreamTour.Model;

namespace ZhouYu.DreamTour.DAL.Repository
{
    public class TrafficRepository: ITrafficRepository
    {
        #region 获取交通路线信息集合
        /// <summary>
        /// 获取交通路线信息集合
        /// </summary>
        /// <returns></returns>
        public List<RouteInfoSimulation> GetRouteInfos(string TranType, string StartAddress, string StopAddress, string CarNumber, out List<RouteInfoSimulation> RouteListone, out List<RouteInfoSimulation> RouteListtwo)
        {
            #region 查询航班集合
            //sql语句
            string sqlzero = @"SELECT co.CompanyNumber AS CompanyNumber,co.CompanyName AS CompanyName,ci.City AS StartCity,citwo.City AS StopCity,r.StartTime AS StartTime,r.StopTime AS StopTime,r.CabinType AS CabinType,r.Price AS Price,r.Number AS Number
                        FROM RouteInfo AS r
                        INNER JOIN Company AS co ON r.`CompanyId`=co.`Id`
                        INNER JOIN CityLocation AS ci ON ci.`Id`=r.`FromId`
                        INNER JOIN CityLocation AS citwo ON citwo.`Id`=r.`DestinationId`
                        INNER JOIN Transportation AS t ON t.`Id`=r.`TranStorpationId` WHERE r.`Difference`=0";
            #endregion
            #region 查询指定日期高铁和火车
            //sql语句
            string sqlone = @"SELECT co.CompanyNumber AS CompanyNumber,co.CompanyName AS CompanyName,ci.City AS StartCity,citwo.City AS StopCity,r.StartTime AS StartTime,r.StopTime AS StopTime,r.CabinType AS CabinType,r.Price AS Price,r.Number AS Number
                        FROM RouteInfo AS r
                        INNER JOIN Company AS co ON r.`CompanyId`=co.`Id`
                        INNER JOIN CityLocation AS ci ON ci.`Id`=r.`FromId`
                        INNER JOIN CityLocation AS citwo ON citwo.`Id`=r.`DestinationId`
                        INNER JOIN Transportation AS t ON t.`Id`=r.`TranStorpationId` WHERE r.`Difference`=1";
            //进行判断
            if (StartAddress != null && StartAddress != "") sqlone += " AND ci.City LIKE '%" + StartAddress + "%'";
            if (StopAddress != null && StopAddress != "") sqlone += " AND citwo.City LIKE '%" + StopAddress + "%'";
            if (CarNumber != null && CarNumber != "") sqlone += " AND co.CompanyNumber LIKE '%" + CarNumber + "%'";
            if (TranType != null && TranType != "") sqlone += " AND t.TranName ='" + TranType + "'";
            #endregion
            #region 查询指定日期高铁和火车
            //sql语句
            string sqltwo = @"SELECT co.CompanyNumber AS CompanyNumber,co.CompanyName AS CompanyName,ci.City AS StartCity,citwo.City AS StopCity,r.StartTime AS StartTime,r.StopTime AS StopTime,r.CabinType AS CabinType,r.Price AS Price,r.Number AS Number
                        FROM RouteInfo AS r
                        INNER JOIN Company AS co ON r.`CompanyId`=co.`Id`
                        INNER JOIN CityLocation AS ci ON ci.`Id`=r.`FromId`
                        INNER JOIN CityLocation AS citwo ON citwo.`Id`=r.`DestinationId`
                        INNER JOIN Transportation AS t ON t.`Id`=r.`TranStorpationId` WHERE r.`Difference`=2";
            #endregion
            DynamicParameters dynamicParameters = null;
            //调用查询集合方法
            List<RouteInfoSimulation> RouteListzero = Show(DapperHelper<RouteInfoSimulation>.QueryList(sqlzero, dynamicParameters));
            RouteListone = Show(DapperHelper<RouteInfoSimulation>.QueryList(sqlone, dynamicParameters));
            RouteListtwo = Show(DapperHelper<RouteInfoSimulation>.QueryList(sqltwo, dynamicParameters));
            return RouteListzero;
        }
        #endregion


        #region 循环将集合时间格式变成只显示时钟与分钟,并计算两个日期的时间差
        /// <summary>
        /// 循环将集合时间格式变成只显示时钟与分钟,并计算两个日期的时间差的方法
        /// </summary>
        public List<RouteInfoSimulation> Show(List<RouteInfoSimulation> RouteList)
        {
            //循环将集合时间格式变成只显示时钟与分钟,并计算两个日期的时间差
            foreach (var item in RouteList)
            {
                //计算两个日期的时间差
                TimeSpan ts = DateTime.Parse(item.StartTime).Subtract(DateTime.Parse(item.StopTime));
                string strHours = ts.Hours + " 小时" + ts.Minutes + "分";
                item.currtlyTime = strHours.Replace("-", "");
                //提取时分方法
                DateTime dt = DateTime.Parse(item.StartTime);
                string str = dt.ToString("HH:mm");
                item.StartTime = str;
                DateTime dt2 = DateTime.Parse(item.StopTime);
                string str2 = dt2.ToString("HH:mm");
                item.StopTime = str2;
            }
            return RouteList;
        }
        #endregion
    }
}
